package sctu.edu.java.year2016.team08.lisonghong.day20160616.chapter17_4;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 松红 on 2016/6/16.
 */
public class Inquire {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("找不到驱动类");
        }
        try {
            String url = "jdbc:mysql://localhost:3306/lsongh";
            String user = "root";
            String Password = "";
            Connection conn = DriverManager.getConnection(url, user, Password);
            String sql = "SELECT * FROM Sex where sex='m'";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery(sql);
            List<Student> is = new ArrayList<Student>();
            int id;
            String name, sex;
            System.out.println("id\tname\tsex");
            while (rs.next()) {
                id = rs.getInt(1);
                name = rs.getString(2);
                sex = rs.getString(3);
                System.out.println(id + "\t" + name + "\t" + sex);
                Student stu = new Student();
                stu.setid(rs.getInt(1));
                stu.setname(rs.getString(2));
                stu.setsex(rs.getString(3));
                is.add(stu);
                System.out.println("一共有男性" + is.size() + "人！");
            }

            String sql1 = "SELECT * FROM Sex where sex='w'";
            PreparedStatement ps1 = conn.prepareStatement(sql1);
            ResultSet rs1 = ps.executeQuery(sql1);
            List<Student> is1 = new ArrayList<Student>();
            int id1;
            String name1, sex1;
            System.out.println("id\tname\tsex");
            while (rs1.next()) {
                id1 = rs1.getInt(1);
                name1 = rs1.getString(2);
                sex1 = rs1.getString(3);
                System.out.println(id1 + "\t" + name1 + "\t" + sex1);
                Student stu1 = new Student();
                stu1.setid(rs1.getInt(1));
                stu1.setname(rs1.getString(2));
                stu1.setsex(rs1.getString(3));
                is1.add(stu1);
                System.out.println("一共有女性" + is.size() + "人！");

            }
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    private static class Student {
        private int id;
        private String name;
        private String sex;

        public void setid(int id) {
            this.id = id;
        }

        public void setname(String name) {
            this.name = name;
        }

        public void setsex(String sex) {
            this.sex = sex;
        }
    }
}
